'''
Author: admin@attacker.club
Date: 2022-07-12 14:39:08
LastEditTime: 2022-09-21 11:18:47
Description: sls查询
pip install -i  http://mirrors.aliyun.com/pypi/simple  --trusted-host mirrors.aliyun.com  
pip install aliyun-log-python-sdk pandas  xlwt xlrd xlutils

'''


import re

from cmath import log
import time
import datetime
from aliyun.log import LogClient, GetLogsRequest
import os
import sys
import os.path as op
level5 = op.abspath(op.join(__file__, op.pardir, op.pardir, op.pardir, op.pardir, op.pardir))
sys.path.append(level5)
from globalConfig import ALIYUN_CONFIG,WXWork_CONFIG
from wxWork import WXWork_SMS


import xlwt
import xlrd
from xlutils.copy import copy
import pandas as pd
import openpyxl

from collections import Counter

from customLog import log_start
logger = log_start('log-search')



# 写入表格
def write_excel_xls(path, sheet_name, value):
    index = len(value)  # 获取需要写入数据的行数
    workbook = xlwt.Workbook()  # 新建一个工作簿
    sheet = workbook.add_sheet(sheet_name)  # 在工作簿中新建一个表格
    for i in range(0, index):
        for j in range(0, len(value[i])):
            sheet.write(i, j, value[i][j])  # 像表格中写入数据（对应的行和列）
    workbook.save(path)  # 保存工作簿
    # logger.info("xls格式表格数据写入!")

#写入数据
def write_excel_xls_append(path, value):
    index = len(value)  # 获取需要写入数据的行数
    workbook = xlrd.open_workbook(path)  # 打开工作簿
    sheets = workbook.sheet_names()  # 获取工作簿中的所有表格
    worksheet = workbook.sheet_by_name(sheets[0])  # 获取工作簿中所有表格中的的第一个表格
    rows_old = worksheet.nrows  # 获取表格中已存在的数据的行数
    new_workbook = copy(workbook)  # 将xlrd对象拷贝转化为xlwt对象
    new_worksheet = new_workbook.get_sheet(0)  # 获取转化后工作簿中的第一个表格
    for i in range(0, index):
        for j in range(0, len(value[i])):
            new_worksheet.write(i + rows_old, j, value[i][j])  # 追加写入数据，注意是从i+rows_old行开始写入
    new_workbook.save(path)  # 保存工作簿
    logger.info("xls格式表格数据更新完毕")

# 通过SQL查询日志。
def query_logs( account,project,logstore,accessKeyId, accessKey,queryTime,domian ):
    
    ## 查询耗时超1m的慢接口语句
    query = f"status = 200 and host not like 'prod-im-ws.ramboplay.com' \
        | select host,url,count(url) as pv ,round(avg(upstream_response_time)) as reqTime \
        where host in ({domian})    \
        group by host,url HAVING reqTime >0.2    \
        ORDER by reqTime  DESC  "    # count(url) >2 
    logger.error(query)
    
    ## 执行sls查询
    client = LogClient(endpoint, accessKeyId, accessKey)
    request = GetLogsRequest(project, logstore, from_time, to_time, query=query)
    response = client.get_logs(request)
    
    
    host_list = []
    uniq_url = []
    for log in response.get_logs():
        ret_list = []
        uniq_ret_list = []
        for k, v in log.contents.items():
            # print("%s : %s" % (k, v))

            ## 域名统计
            if k == 'host':
                host_list.append(v)
            ## 所有接口
            ret_list.append(v)
            
            if k == 'url':
                matchObj=re.match(r'(.*?\w+=)?',v)
                if matchObj.group():
                    v = f'{matchObj.group(1)}?????'

            uniq_ret_list.append(v)
        uniq_url.append(uniq_ret_list)
         
        full_url.append(ret_list) 
       

    host = Counter(host_list) #  dict_items([('trade-api.c5game.com', 33), ('prod-gateway.c5game.com', 1), ('inventory.c5game.com', 1)])
    ret = "".join([' {0} {1}条\n>'.format(x,y) for x,y in host.items() ]) # 反向推到式生成字符串拼接
 
 
    
    ## 直接处理数据写入表格
    book_name_xls = f'{account}数据统计{day}.xls'
    sheet_name_xls = f'ingrees{queryTime}H'
    value_title = [["域名", "接口", "PV", "耗时"], ]
    write_excel_xls(book_name_xls, sheet_name_xls, value_title) 
    write_excel_xls_append(book_name_xls, full_url)

    ## 写入汇总数据
    book_name_xls_new = f'{account}过滤{day}.xls'
    sheet_name_xls_new = f'ingrees{queryTime}H'
    value_title = [["域名", "接口", "PV", "耗时"], ]
    write_excel_xls(book_name_xls_new, sheet_name_xls_new, value_title) 
    write_excel_xls_append(book_name_xls_new, uniq_url)

    # 读取Excel中Sheet数据处理
    Result1=pd.DataFrame(pd.read_excel(book_name_xls, sheet_name_xls)) 
    data=pd.DataFrame(pd.read_excel(book_name_xls_new, sheet_name_xls_new)) 
    Result2 = data.drop_duplicates(['接口']) # 对接口列去重
    
    writer = pd.ExcelWriter(book_name_xls)
    Result2.to_excel(writer,sheet_name='数据去重',index=False)
    Result1.to_excel(writer,sheet_name='历史数据',index=False)
    writer.save()
    writer.close()
    logger.info("完成数据去重")
    return ret


if __name__ == '__main__':

    queryTime = 168 # 查询24小时数据
    to_time = time.time() # 当前时间
    t2=(datetime.datetime.now()-datetime.timedelta(hours=queryTime)).strftime("%Y-%m-%d %H:%M:%S")
    from_time=time.mktime(time.strptime(t2, '%Y-%m-%d %H:%M:%S'))  # 转为秒级时间戳; hours=24小时前
    day = datetime.datetime.fromtimestamp(to_time).strftime("%Y%m%d")

    endpoint = "cn-shanghai.log.aliyuncs.com"  # 地区
    result = {}
    ## 遍历云平台账号信息
    for account in ALIYUN_CONFIG:
        accessKeyId = ALIYUN_CONFIG[account]['accesskey']
        accessKey= ALIYUN_CONFIG[account]['secret']
        domian =  ",".join(["'{}'".format(i) for i in ALIYUN_CONFIG[account]['domian']])
        info = ("当前账号: [ {0} ] AK账号: {1}".format(account,accessKeyId) )
        logger.warning(info)
        logger.info(f'关联域名:{domian}')
        
        for project in  ALIYUN_CONFIG[account]['sls']:
            logstore = ALIYUN_CONFIG[account]['sls'][project]
            logger.info(f"日志project: {project}")
            # 执行查询任务
            full_url = []
            ret = query_logs( account,project,logstore,accessKeyId, accessKey,queryTime,domian)

        ## 企业微信推送消息
        wxkey = ALIYUN_CONFIG[account]['wxkey']
        data = f'''
        ## **{account} 慢接口统计** 
        >接口数量：<font color="warning">{len(full_url)}</font>
        >{ret}<font color="info">采集{queryTime}小时内,响应耗时1m的接口</font>
        '''
        # <@Logan> 

        sms = WXWork_SMS(data,wxkey)    
        sms.send_msg_markdown()
        file = f'{account}数据统计{day}.xls'
        sms.send_upload(file)
    